#!/usr/bin/python

"""
::BOH
$Id: db-installer.py,v 1.0 2007/04/27 00:00:00

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or (at
your option) any later version.

This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
::EOH

Database Installer
"""
import sys

print "OWTF Database Installer"
print "I am going to ask you a series of questions.\nSuggested values will be noted in [brackets]."

method = raw_input("Do you want me to use your OWTF configuration file (yes or no)? ")
method  = (method.lower()).strip()

if method == "yes":
    try:
        from OWTFparser import *
    except:
        print "Error: Can't find OWTFparser.py!! Now Exiting."
        sys.exit(1)

    conflocation = raw_input("Enter the path and filename of your OWTF config file ")
    try:
        print "Reading Configuration File..."
        init = HandleXML()
        config = ConfScanner(init.ParseFromFile(conflocation))
        print "Configuration file read."
        if config.general_db[0].strip() == "mysql":
            try:
                import MySQLdb
            except:
                print "MySQL-Python not installed."
                sys.exit(1)
            host = config.general_db[1].strip()
            # DISABLED FOR NOW.
            #
            #
            #port = config.general_db[2].strip()
            #
            #
            user = config.general_db[3].strip()
            passwd = config.general_db[4].strip()
            db_name = config.general_db[5].strip()
            print "Connecting to database..."
            ms = MySQLdb.connect(host,user,passwd)
            cur = ms.cursor()
            print "Connected to database."
            print "Creating Database..."
            cur.execute("""CREATE DATABASE IF NOT EXISTS %s""",(db_name))
            cur.execute("""USE %s""",(db_name))
            cur.execute("""DROP TABLE IF EXISTS 'alert';""")
            cur.execute("""DROP TABLE IF EXISTS 'alertables';""")
            cur.execute("""DROP TABLE IF EXISTS 'monitors';""")
            cur.execute("""DROP TABLE IF EXISTS 'sensor_data';""")
            cur.execute("""DROP TABLE IF EXISTS 'sensors';""")
            cur.execute("""CREATE TABLE 'alert' ( 'sid' varchar(16) NOT NULL, 'cron' datetime NOT NULL, 'level' char(1) NOT NULL, 'event' varchar(4) NOT NULL, PRIMARY KEY  ('sid','cron','event')) ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'alertables' ( 'sid' varchar(16) NOT NULL, 'temp_min' double default NULL, 'temp_max' double default NULL, 'humid_min' double default NULL, 'humid_max' double default NULL, 'v1_min' double default NULL, 'v1_max' double default NULL, 'v2_min' double default NULL, 'v2_max' double default NULL, PRIMARY KEY  ('sid'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'monitors' ( 'mid' varchar(20) NOT NULL, 'cron' datetime NOT NULL, PRIMARY KEY  ('mid'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'sensor_data' ( 'sid' varchar(16) NOT NULL, 'cron' datetime NOT NULL, 'temp' double default NULL, 'humid' double default NULL, 'v1' double default NULL, 'v2' double default NULL, PRIMARY KEY  ('sid','cron'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'sensors' ( 'mid' varchar(20) NOT NULL, 'sid' varchar(16) NOT NULL, 'location' varchar(25) default NULL, 'sensortype' integer NOT NULL, 'multisensor' varchar(4) default NULL, 'cron' datetime default NULL, 'active' char(1) NOT NULL default 'Y', PRIMARY KEY  ('mid','sid'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            ms.close()
            print "The database has been created :-D"
            
        else:
            location = config.general_db[1].strip()
            if location == ":memory:":
                print "This installer cannot create databases in :memory:"
                print "The OWTF Database Installer is now exiting."
                sys.exit(0)
            db_name = config.general_db[2].strip()
            location = (location + "/" + db_name).strip()
            try:
                print "Connecting to database..."
                if sys.version_info[1] > 4:
                    import sqlite3
                    ms = sqlite3.connect(location)
                else:
                    from pysqlite2 import dbapi2 as sqlite
                    ms = sqlite.connect(location)
                cur = ms.cursor()
                print "Connected to database."
                cur = ms.cursor()
                cur.execute("""DROP TABLE IF EXISTS 'alert';""")
                cur.execute("""DROP TABLE IF EXISTS 'alertables';""")
                cur.execute("""DROP TABLE IF EXISTS 'monitors';""")
                cur.execute("""DROP TABLE IF EXISTS 'sensor_data';""")
                cur.execute("""DROP TABLE IF EXISTS 'sensors';""")
                cur.execute("""CREATE TABLE 'alert' ( 'sid' text NOT NULL, 'cron' text NOT NULL, 'level' text NOT NULL, 'event' text NOT NULL, PRIMARY KEY  ('sid','cron','event'));""")
                cur.execute("""CREATE TABLE 'alertables' ( 'sid' text NOT NULL, 'temp_min' real default NULL, 'temp_max' real default NULL, 'humid_min' real default NULL, 'humid_max' real default NULL, 'v1_min' real default NULL,'v1_max' real default NULL,'v2_min' real default NULL, 'v2_max' real default NULL, PRIMARY KEY  ('sid'));""")
                cur.execute("""CREATE TABLE 'monitors' ( 'mid' text NOT NULL, 'cron' text NOT NULL, PRIMARY KEY  ('mid'));""")
                cur.execute("""CREATE TABLE 'sensor_data' ( 'sid' text NOT NULL, 'cron' text NOT NULL, 'temp' real default NULL, 'humid' real default NULL, 'v1' real default NULL, 'v2' real default NULL, PRIMARY KEY  ('sid','cron'));""")
                cur.execute("""CREATE TABLE 'sensors' ('mid' text NOT NULL, 'sid' text NOT NULL, 'location' text default NULL, 'sensortype' integer NOT NULL, 'multisensor' text default NULL,'cron' text default NULL, 'active' text NOT NULL default 'Y', PRIMARY KEY  ('mid','sid'));""")
                ms.close()
                print "The database has been created :-D"
                
            except:
                print "SQLite Error"
                sys.exit(1)
    except:
        print "Can't access your configuration file.  Now exiting"
        sys.exit(1)

else:
    dbtype = raw_input("Which database server do you wish to use: MySQL or SQLite3 ? ")
    dbtype = (dbtype.lower()).strip()
    if dbtype == "mysql":
        try:
            import MySQLdb
            host = raw_input("What is the host name of your MySQL server? [127.0.0.1] ")
            host = host.strip()
            if host == "":
                host = "127.0.0.1"
            user = raw_input("What is the user name you wish to use for the install? [root] ")
            user = user.strip()
            if user =="":
                user = "root"
            passwd = raw_input("What is the password for the user ID you provided in the last question? ")
            passwd = passwd.strip()
            if passwd == "":
                passwd = ""
            print "The name of the database will be OWTF"
            #db_name = db_name.strip()
            #if db_name =="":
            db_name ="owtf"
            print "Connecting to database..."
            ms = MySQLdb.connect(host,user,passwd)
            cur = ms.cursor()
            print "Connected to database."
            print "Creating Database..."
            cur.execute("""CREATE DATABASE IF NOT EXISTS OWTF""")
            print "created"
            cur.execute("""USE OWTF;""")
            print "using"
            cur.execute("""DROP TABLE IF EXISTS 'alert';""")
            cur.execute("""DROP TABLE IF EXISTS 'alertables';""")
            cur.execute("""DROP TABLE IF EXISTS 'monitors';""")
            cur.execute("""DROP TABLE IF EXISTS 'sensor_data';""")
            cur.execute("""DROP TABLE IF EXISTS 'sensors';""")
            print "dropped"
            cur.execute("""CREATE TABLE 'alert' ( 'sid' varchar(16) NOT NULL, 'cron' datetime NOT NULL, 'level' char(1) NOT NULL, 'event' varchar(4) NOT NULL, PRIMARY KEY  ('sid','cron','event')) ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'alertables' ( 'sid' varchar(16) NOT NULL, 'temp_min' double default NULL, 'temp_max' double default NULL, 'humid_min' double default NULL, 'humid_max' double default NULL, 'v1_min' double default NULL, 'v1_max' double default NULL, 'v2_min' double default NULL, 'v2_max' double default NULL, PRIMARY KEY  ('sid'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'monitors' ( 'mid' varchar(20) NOT NULL, 'cron' datetime NOT NULL, PRIMARY KEY  ('mid'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'sensor_data' ( 'sid' varchar(16) NOT NULL, 'cron' datetime NOT NULL, 'temp' double default NULL, 'humid' double default NULL, 'v1' double default NULL, 'v2' double default NULL, PRIMARY KEY  ('sid','cron'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            cur.execute("""CREATE TABLE 'sensors' ( 'mid' varchar(20) NOT NULL, 'sid' varchar(16) NOT NULL, 'location' varchar(25) default NULL, 'sensortype' integer NOT NULL, 'multisensor' varchar(4) default NULL, 'cron' datetime default NULL, 'active' char(1) NOT NULL default 'Y', PRIMARY KEY  ('mid','sid'))ENGINE=MyISAM DEFAULT CHARSET=utf8;""")
            ms.close()
            print "The database has been created :-D"
        except:
            print "MySQL-Python Error."
            sys.exit(1)
    else:
        try:
            location = raw_input("Where should the new SQLite database be created? ")
            if location == ":memory:":
                print "This installer cannot create databases in :memory:"
                print "The OWTF Database Installer is now exiting."
                sys.exit(0)
            db_name = raw_input("What is the name of the database? [owtf] ")
            if db_name =="":
                db_name ="owtf"
            location = (location + "/" + db_name).strip()
            print "Connecting to database..."
            if sys.version_info[1] > 4:
                import sqlite3
                ms = sqlite3.connect(location)
            else:
                from pysqlite2 import dbapi2 as sqlite
                ms = sqlite.connect(location)
            cur = ms.cursor()
            print "Connected to database."
            cur = ms.cursor()
            cur.execute("""DROP TABLE IF EXISTS 'alert';""")
            cur.execute("""DROP TABLE IF EXISTS 'alertables';""")
            cur.execute("""DROP TABLE IF EXISTS 'monitors';""")
            cur.execute("""DROP TABLE IF EXISTS 'sensor_data';""")
            cur.execute("""DROP TABLE IF EXISTS 'sensors';""")
            cur.execute("""CREATE TABLE 'alert' ( 'sid' text NOT NULL, 'cron' text NOT NULL, 'level' text NOT NULL, 'event' text NOT NULL, PRIMARY KEY  ('sid','cron','event'));""")
            cur.execute("""CREATE TABLE 'alertables' ( 'sid' text NOT NULL, 'temp_min' real default NULL, 'temp_max' real default NULL, 'humid_min' real default NULL, 'humid_max' real default NULL, 'v1_min' real default NULL,'v1_max' real default NULL,'v2_min' real default NULL, 'v2_max' real default NULL, PRIMARY KEY  ('sid'));""")
            cur.execute("""CREATE TABLE 'monitors' ( 'mid' text NOT NULL, 'cron' text NOT NULL, PRIMARY KEY  ('mid'));""")
            cur.execute("""CREATE TABLE 'sensor_data' ( 'sid' text NOT NULL, 'cron' text NOT NULL, 'temp' real default NULL, 'humid' real default NULL, 'v1' real default NULL, 'v2' real default NULL, PRIMARY KEY  ('sid','cron'));""")
            cur.execute("""CREATE TABLE 'sensors' ('mid' text NOT NULL, 'sid' text NOT NULL, 'location' text default NULL, 'sensortype' integer NOT NULL, 'multisensor' text default NULL,'cron' text default NULL, 'active' text NOT NULL default 'Y', PRIMARY KEY  ('mid','sid'));""")
            ms.close()
            print "The database has been created :-D"
            
        except:
            print "SQLite error"
            sys.exit(1)

print "The OWTF Database Installer is now exiting."
sys.exit(0)

